import json

from flask import Flask
from flask import render_template, request, session, url_for, redirect, flash, g, jsonify
import sqlite3
import config
from flask_cors import *



app = Flask(__name__)
CORS(app, supports_credentials=True)
app.config.from_object('config')
app.config.from_envvar('FLASK_SETTINGS', silent=True)

# @app.before_request
# def before_request():
#     g.db = sqlite3.connect(app.config['DATABASE'])
#
# @app.teardown_request
# def teardown_request(exception):
#     db = getattr(g, 'db', None)
#     if db is not None:
#         db.close()

def get_db():
    db = sqlite3.connect('mydb.db')        # 打开一个数据库文件，返回connect对象
    db.row_factory = sqlite3.Row       # 一个行工厂（ row factory ）可以简化 SQLite 的使用
                                    # Row对象是namedtuple,因此既可以通过索引访问也以通过键访问
    #print(db)
    return db
def query_db(query, args=(), one=False):
    db = get_db()
    cur = db.execute(query, args)
    db.commit()                            # 提交数据
    rv = cur.fetchall()                    # 返回多条数据，getchone：返回一条数据
    db.close()
    # 如果rv为真，返回rv[0]，否则返回none
    # 返回rv
    #print(cur)
    #print(rv)
    return (rv[0] if rv else None) if one else rv

def insert_db():
    conn = sqlite3.connect('mydb.db')  # 创建数据库
    conn.text_factory = str  # Python和SQLite类型转换不统一，将数据库连接的text_factory属性设置为str
    c = conn.cursor()  # 连接数据库
    return c

def str_to_bool(str):
    if str == 'True':
        return True
    else:
        return False


@app.route('/users', methods=['GET','POST'])
def users():
    user = request.get_json()
    print(user)
    print(user['username'])
    print(user['password'])
    sql = "select * from users where name = "+"'"+user['username']+"'"+" and password ="+"'"+user['password']+"'"
    print("sql="+sql)
    rows = query_db(sql)
    rows = [dict(row) for row in rows]
    return jsonify(rows)

@app.route('/messages', methods=['GET','POST'])
def messages():
    conn = sqlite3.connect('mydb.db')  # 创建数据库
    conn.text_factory = str  # Python和SQLite类型转换不统一，将数据库连接的text_factory属性设置为str
    c = conn.cursor()  # 连接数据库

    message = request.get_json()
    # print(message)
    message_date = []
    sum_data = []
    for i in message.values():
        # print(i)
        message_date.append(i)
    # print(tuple(message_date))
    sum_data.append(tuple(message_date))
    # print(sum_data)

    # c.execute('''DROP TABLE IF EXISTS messages''')
    # c.execute('''CREATE TABLE message(
    #     name text not null,
    #     region text not null,
    #     date1 text not null,
    #     date2 text not null,
    #     delivery text not null,
    #     type text not null,
    #     resource text not null,
    #     desc text not null)''')

    c.executemany('insert into message (name, region, date1, date2, delivery, type, resource, desc) values'
                  ' (?, ?, ?, ?, ?, ?, ?, ?)', sum_data)
    conn.commit()
    conn.close()

    sql = "select * from message"
    rows = query_db(sql)
    rows = [dict(row) for row in rows]
    print(rows)
    return jsonify(rows)

# 用例表
@app.route('/give', methods = ['GET', 'POST'])
def give():
    if request.method == 'POST':
        message = request.get_json()

        conn = sqlite3.connect('mydb.db')  # 创建数据库
        conn.text_factory = str  # Python和SQLite类型转换不统一，将数据库连接的text_factory属性设置为str
        c = conn.cursor()  # 连接数据库
        c.execute('''drop table if exists point''')
        c.execute('''create table point(
            point_id integer primary key autoincrement,
            id char(10) not null,
            label text not null,
            IsShow text not null,
            num_01 text not null,
            num_02 text not null,
            num_03 text not null)''')
        num_01 = 0
        num_02 = 0
        num_03 = 0
        for i in message:
            num_01 += 1
            num_02 = 0
            num_03 = 0
            data = []
            print(tuple([i['id']]+[i['label']]+[i['IsShow']]+[num_01]+[0]+[0]))
            data.append(tuple([i['id']]+[i['label']]+[i['IsShow']]+[num_01]+[0]+[0]))
            c.executemany('insert into point (id, label, IsShow, num_01, num_02, num_03) values'
                          ' (?, ?, ?, ?, ?, ?)', data)
            data = []
            if len(i) == 4:
                for j in i['children']:
                    num_02 += 1
                    print(j['id'], j['label'], j['IsShow'], num_01, num_02, 0)
                    data.append(tuple([j['id']] + [j['label']] + [j['IsShow']] + [num_01] + [num_02] + [0]))
                    c.executemany('insert into point (id, label, IsShow, num_01, num_02, num_03) values'
                                  ' (?, ?, ?, ?, ?, ?)', data)
                    data = []
                    if len(j) == 4 and j['children'] != []:
                        for k in j['children']:
                            num_03 += 1
                            print(k['id'], k['label'], k['IsShow'], num_01, num_02, num_03)
                            data.append(tuple([k['id']] + [k['label']] + [k['IsShow']] + [num_01] + [num_02] + [num_03]))
                            c.executemany('insert into point (id, label, IsShow, num_01, num_02, num_03) values'
                                          ' (?, ?, ?, ?, ?, ?)', data)
                            data = []
        conn.commit()
        conn.close()
        sql = "select * from point"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        print(rows)
        return jsonify(rows)


@app.route('/point_view', methods = ['GET', 'POST'])
def point_view():
    if request.method == 'GET':
        sql = "select id,label,IsShow,num_01,num_02,num_03 from point"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        new_rows = []

        for i in rows:
            if i['num_02'] == '0':
                new_rows.append(i)

        for i in rows:
            if i['num_03'] == '0' and i['num_02'] != '0':
                n1 = int(i['num_01'])
                if len(new_rows[n1-1]) == 7:
                    continue
                else:
                    new_rows[n1-1]['children'] = []

        for i in rows:
            if i['num_03'] == '0' and i['num_02'] != '0':
                n1 = int(i['num_01'])
                new_rows[n1-1]['children'].append(i)

        for i in rows:
            if i['num_03'] != '0':
                n1 = int(i['num_01'])
                n2 = int(i['num_02'])
                if len(new_rows[n1-1]['children'][n2-1]) == 7:
                    continue
                else:
                    new_rows[n1-1]['children'][n2-1]['children'] = []

        for i in rows:
            if i['num_03'] != '0':
                n1 = int(i['num_01'])
                n2 = int(i['num_02'])
                new_rows[n1-1]['children'][n2-1]['children'].append(i)

        for i in new_rows:
            i.pop('num_01')
            i.pop('num_02')
            i.pop('num_03')
            i['IsShow'] = False
            if len(i) == 4:
                for j in i['children']:
                    j.pop('num_01')
                    j.pop('num_02')
                    j.pop('num_03')
                    j['IsShow'] = False
                if len(j) == 4:
                    for k in j['children']:
                        k.pop('num_01')
                        k.pop('num_02')
                        k.pop('num_03')
                        k['IsShow'] = False
        return jsonify(new_rows)


@app.route('/test_case', methods = ['GET', 'POST'])
def test_case():
    if request.method == 'GET':
        sql = "select * from test_case"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        for i in rows:
            print(type(i['execute']))
            i['execute'] = str_to_bool(i['execute'])
        for i in rows:
            print(type(i['pass']))
            i['pass'] = str_to_bool(i['pass'])
        return jsonify(rows)
    if request.method == 'POST':

        conn = sqlite3.connect('mydb.db')  # 创建数据库
        conn.text_factory = str  # Python和SQLite类型转换不统一，将数据库连接的text_factory属性设置为str
        c = conn.cursor()  # 连接数据库
        message = request.get_json()
        print(message)
        message_date = []
        sum_data = []
        for i in message.values():
            # print(i)
            message_date.append(i)
        print(tuple(message_date))
        sum_data.append(tuple(message_date))
        print(111)
        print(sum_data)


        c.executemany('insert into test_case '
                      '(case_name, test_id, pro_index, qtys, sjff, init, qwjg, pgbz, pro_members, label, date1, date2)'
                      ' values '
                      '(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', sum_data)
        conn.commit()
        conn.close()

        sql = "select * from test_case"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        for i in rows:
            print(type(i['execute']))
            i['execute'] = str_to_bool(i['execute'])
        for i in rows:
            print(type(i['pass']))
            i['pass'] = str_to_bool(i['pass'])
        return jsonify(rows)


@app.route('/test_case_drop', methods = ['GET', 'POST'])
def test_case_drop():
    if request.method == 'POST':
        message = request.get_json()

        num = message['yl_id']+1
        conn = sqlite3.connect('mydb.db')  # 创建数据库
        conn.text_factory = str  # Python和SQLite类型转换不统一，将数据库连接的text_factory属性设置为str
        c = conn.cursor()  # 连接数据库
        sql_01 = "delete from test_case where yl_id = " + str(num)
        c.execute('''drop table if exists temp''')
        c.execute('''create table temp(
            yl_id integer primary key autoincrement,
            case_name char(50),
            test_id char(50) not null,
            pro_index text not null,
            qtys text not null,
            sjff text not null,
            init text not null,
            qwjg text not null,
            pgbz text not null,
            pro_members char(50) not null,
            label text not null,
            date1 text not null,
            date2 text not null)''')
        sql_02 = "insert into temp(case_name,test_id,pro_index,qtys,sjff,init,qwjg,pgbz,pro_members,label,date1,date2)" \
                 " select case_name,test_id,pro_index,qtys,sjff,init,qwjg,pgbz,pro_members,label,date1,date2 from test_case"
        sql_03 = "drop table test_case"
        sql_04 = "alter table temp rename to test_case"
        c.execute(sql_01)
        c.execute(sql_02)
        c.execute(sql_03)
        c.execute(sql_04)
        conn.commit()
        conn.close()

        sql = "select * from test_case"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        return jsonify(rows)


@app.route('/test_case_open', methods = ['GET', 'POST'])
def test_case_open():
    if request.method == 'POST':
        message = request.get_json()

        tcd_num = message['yl_id']+1
        print(tcd_num)

        sql = "select * from test_case where yl_id = " + str(tcd_num)
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        print(jsonify(rows))
        print(rows)
        return jsonify(rows)


@app.route('/test_case_edit', methods = ['GET', 'POST'])
def test_case_edit():
    if request.method == 'POST':
        message = request.get_json()

        conn = sqlite3.connect('mydb.db')  # 创建数据库
        conn.text_factory = str  # Python和SQLite类型转换不统一，将数据库连接的text_factory属性设置为str
        c = conn.cursor()  # 连接数据库
        sql = "update test_case set test_id=" + "'" + message['test_id'] + "'" + "," + \
              "pro_index=" + "'" + message['pro_index'] + "'" + "," + \
              "qtys=" + "'" + message['qtys'] + "'" + "," + \
              "sjff=" + "'" + message['sjff'] + "'" + "," + \
              "init=" + "'" + message['init'] + "'" + "," + \
              "qwjg=" + "'" + message['qwjg'] + "'" + "," + \
              "pgbz=" + "'" + message['pgbz'] + "'" + "," + \
              "pro_members=" + "'" + message['pro_members'] + "'" + "," + \
              "date1=" + "'" + message['date1'] + "'" + "," + \
              "date2=" + "'" + message['date2'] + "'" + "," + \
              "label=" + "'" + message['label'] + "'" \
              + "where case_name=" + "'" + message['case_name'] + "'"
        print(sql)
        c.execute(sql)
        conn.commit()
        conn.close()

        sql = "select * from test_case"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        return jsonify(rows)


@app.route('/test_case_query', methods = ['GET', 'POST'])
def test_case_query():
    if request.method == 'POST':
        message = request.get_json()
        # print(message)
        if message['case_name'] == '':
            sql = "select * from test_case"
        else:
            sql = "select * from test_case where case_name = " + "'"+message['case_name']+"'"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        return jsonify(rows)


@app.route('/test_case_index', methods = ['GET', 'POST'])
def test_case_index():
    if request.method == 'GET':
        sql = "select pro_index from item"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        return jsonify(rows)


@app.route('/get_point', methods = ['GET', 'POST'])
def get_point():
    if request.method == 'GET':
        sql = "select label from point"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        return jsonify(rows)


@app.route('/point_case', methods = ['GET', 'POST'])
def point_case():
    if request.method == 'POST':
        message = request.get_json()
        sql = "select * from test_case where label=" + "'" + message['label'] + "'"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        return jsonify(rows)


@app.route('/edit_estate', methods = ['GET', 'POST'])
def edit_estate():
    if request.method == 'POST':
        message = request.get_json()
        conn = sqlite3.connect('mydb.db')  # 创建数据库
        conn.text_factory = str  # Python和SQLite类型转换不统一，将数据库连接的text_factory属性设置为str
        c = conn.cursor()  # 连接数据库
        sql = "update test_case set execute=" + "'" + str(message['execute']) + "'" +\
              "where case_name=" + "'" + message['case_name'] + "'"
        # print(sql)
        c.execute(sql)
        conn.commit()
        conn.close()

        sql_01 = "select * from test_case"
        rows = query_db(sql_01)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        for i in rows:
            print(type(i['execute']))
            i['execute'] = str_to_bool(i['execute'])
        return jsonify(rows)


@app.route('/edit_pstate', methods = ['GET', 'POST'])
def edit_pstate():
    if request.method == 'POST':
        message = request.get_json()
        conn = sqlite3.connect('mydb.db')  # 创建数据库
        conn.text_factory = str  # Python和SQLite类型转换不统一，将数据库连接的text_factory属性设置为str
        c = conn.cursor()  # 连接数据库
        sql = "update test_case set pass=" + "'" + str(message['pass']) + "'" +\
              "where case_name=" + "'" + message['case_name'] + "'"
        # print(sql)
        c.execute(sql)
        conn.commit()
        conn.close()

        sql_01 = "select * from test_case"
        rows = query_db(sql_01)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        for i in rows:
            print(type(i['execute']))
            i['execute'] = str_to_bool(i['execute'])
        for i in rows:
            print(type(i['pass']))
            i['pass'] = str_to_bool(i['pass'])
        return jsonify(rows)


# 用户表
@app.route('/users_view', methods=['GET', 'POST'])
def users_view():
    if request.method == 'GET':
        sql = "select name,email,mobile,role_name,mg_state from users"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        return jsonify(rows)


@app.route('/users_viewl', methods=['GET', 'POST'])
def users_viewl():
    if request.method == 'GET':
        sql = "select name from users where role_name = '组管理员'"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        return jsonify(rows)


@app.route('/users_viewm', methods=['GET', 'POST'])
def users_viewm():
    if request.method == 'GET':
        sql = "select name from users where role_name = '测试工程师'"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        return jsonify(rows)


# 项目表



@app.route('/item_add', methods=['GET', 'POST'])
def item_add():
    if request.method == 'POST':
        message = request.get_json()

        conn = sqlite3.connect('mydb.db')  # 创建数据库
        conn.text_factory = str  # Python和SQLite类型转换不统一，将数据库连接的text_factory属性设置为str
        c = conn.cursor()  # 连接数据库
        message_data = []
        sum_data = []
        for i in message.values():
            if isinstance(i, list) is True:
                list_01 = [str(j) for j in i]
                i = ', '.join(list_01)
            message_data.append(i)
        sum_data.append(tuple(message_data))
        c.executemany('insert into item '
                      '(pro_name,pro_index,pro_system,pro_leader,pro_members,pro_starttime,'
                      'pro_endtime,pro_level,pro_state,pro_description) values'
                      ' (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', sum_data)
        conn.commit()
        conn.close()

        sql = "select * from item"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        return jsonify(rows)


@app.route('/item_drop', methods=['GET', 'POST'])
def item_drop():
    if request.method == 'POST':
        message = request.get_json()

        num = message['pro_id']+1
        conn = sqlite3.connect('mydb.db')  # 创建数据库
        conn.text_factory = str  # Python和SQLite类型转换不统一，将数据库连接的text_factory属性设置为str
        c = conn.cursor()  # 连接数据库
        sql_01 = "delete from item where pro_id = " + str(num)
        c.execute('''drop table if exists temp_03''')
        c.execute('''create table temp_03(
            pro_id integer primary key autoincrement,
            pro_name char(50) not null,
            pro_index char(50) not null,
            pro_system text not null,
            pro_leader char(50) not null,
            pro_members char(50) not null,
            pro_starttime text not null,
            pro_endtime text not null,
            pro_level text not null,
            pro_state text not null,
            pro_description text not null)''')
        sql_02 = "insert into temp_03(pro_name,pro_index,pro_system,pro_leader,pro_members," \
                 "pro_starttime,pro_endtime,pro_level,pro_state,pro_description)" \
                 " select pro_name,pro_index,pro_system,pro_leader,pro_members," \
                 "pro_starttime,pro_endtime,pro_level,pro_state,pro_description from item"
        sql_03 = "drop table item"
        sql_04 = "alter table temp_03 rename to item"
        c.execute(sql_01)
        c.execute(sql_02)
        c.execute(sql_03)
        c.execute(sql_04)
        conn.commit()
        conn.close()

        sql = "select * from item"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        return jsonify(rows)




@app.route('/item_edit', methods=['GET', 'POST'])
def item_edit():
    if request.method == 'POST':
        message = request.get_json()

        list_01 = [str(j) for j in message['pro_members']]
        message['pro_members'] = ', '.join(list_01)

        conn = sqlite3.connect('mydb.db')  # 创建数据库
        conn.text_factory = str  # Python和SQLite类型转换不统一，将数据库连接的text_factory属性设置为str
        c = conn.cursor()  # 连接数据库
        sql = "update item set pro_index=" + "'" + message['pro_index'] + "'" + "," + \
              "pro_system=" + "'" + message['pro_system'] + "'" + "," + \
              "pro_leader=" + "'" + message['pro_leader'] + "'" + "," + \
              "pro_members=" + "'" + message['pro_members'] + "'" + "," + \
              "pro_starttime=" + "'" + message['pro_starttime'] + "'" + "," + \
              "pro_endtime=" + "'" + message['pro_endtime'] + "'" + "," + \
              "pro_level=" + "'" + message['pro_level'] + "'" + "," + \
              "pro_state=" + "'" + message['pro_state'] + "'" + "," + \
              "pro_description=" + "'" + message['pro_description'] + "'" \
              + "where pro_name=" + "'" + message['pro_name'] + "'"
        print(sql)
        c.execute(sql)
        conn.commit()
        conn.close()

        sql = "select * from item"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        return jsonify(rows)


@app.route('/item_search', methods=['GET', 'POST'])
def item_search():
    if request.method == 'POST':
        message = request.get_json()
        if message['query'] == '':
            sql = "select * from item"
        else:
            sql = "select * from item where pro_name = " + "'" + message['query'] + "'"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        return jsonify(rows)


# 测试计划
@app.route('/plan_view', methods=['GET', 'POST'])
def plan_view():
    if request.method == 'GET':
        sql = "select plan_name,pro_index,pro_system,pro_leader,plan_starttime,plan_endtime from plan"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        return jsonify(rows)


# 添加表数据
# insert into plan (pro_index,pro_system,pro_leader,pro_members,pro_description) select pro_index,pro_system,pro_leader,pro_members,pro_description from item


@app.route('/plan_edit_open', methods=['GET', 'POST'])
def plan_edit_open():
    if request.method == 'POST':
        message = request.get_json()

        num = message['plan_id'] + 1
        sql = "select * from plan where plan_id = " + str(num)
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows[0]['pro_members'])
        if rows[0]['pro_members'] != '':
            rows[0]['pro_members'] = rows[0]['pro_members'].split(', ')
        return jsonify(rows)


@app.route('/plan_edit', methods=['GET', 'POST'])
def plan_edit():
    if request.method == 'POST':
        message = request.get_json()
        print(message)

        # list_01 = [str(j) for j in message['pro_members']]
        # message['pro_members'] = ', '.join(list_01)

        conn = sqlite3.connect('mydb.db')  # 创建数据库
        conn.text_factory = str  # Python和SQLite类型转换不统一，将数据库连接的text_factory属性设置为str
        c = conn.cursor()  # 连接数据库
        sql = "update plan set plan_name=" + "'" + message['plan_name'] + "'" + "," + \
              "pro_file=" + "'" + message['pro_file'] + "'" + "," + \
              "test_yq=" + "'" + message['test_yq'] + "'" + "," + \
              "test_hj=" + "'" + message['test_hj'] + "'" + "," + \
              "test_end=" + "'" + message['test_end'] + "'" + "," + \
              "plan_starttime=" + "'" + message['plan_starttime'] + "'" + "," + \
              "plan_endtime=" + "'" + message['plan_endtime'] + "'" + \
              "where pro_index=" + "'" + message['pro_index'] + "'"
        print(sql)
        c.execute(sql)
        conn.commit()
        conn.close()

        sql = "select * from plan"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        return jsonify(rows)


@app.route('/plan_search', methods=['GET', 'POST'])
def plan_search():
    if request.method == 'POST':
        message = request.get_json()
        if message['query'] == '':
            sql = "select * from plan"
        else:
            sql = "select * from plan where plan_name = " + "'" + message['query'] + "'"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        return jsonify(rows)


# 测试报告
@app.route('/report_view', methods=['GET', 'POST'])
def report_view():
    if request.method == 'GET':
        sql = "select report_name,pro_index,pro_system,pro_leader,test_jg,report_starttime,report_endtime from report"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        return jsonify(rows)


@app.route('/report_edit_open', methods=['GET', 'POST'])
def report_edit_open():
    if request.method == 'POST':
        message = request.get_json()

        num = message['report_id'] + 1
        sql = "select * from report where report_id = " + str(num)
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows[0]['pro_members'])
        if rows[0]['pro_members'] != '':
            rows[0]['pro_members'] = rows[0]['pro_members'].split(', ')
        return jsonify(rows)


@app.route('/report_edit', methods=['GET', 'POST'])
def report_edit():
    if request.method == 'POST':
        message = request.get_json()

        # list_01 = [str(j) for j in message['pro_members']]
        # message['pro_members'] = ', '.join(list_01)

        conn = sqlite3.connect('mydb.db')  # 创建数据库
        conn.text_factory = str  # Python和SQLite类型转换不统一，将数据库连接的text_factory属性设置为str
        c = conn.cursor()  # 连接数据库
        sql = "update report set report_name=" + "'" + message['report_name'] + "'" + "," + \
              "report_file=" + "'" + message['report_file'] + "'" + "," + \
              "test_cl=" + "'" + message['test_cl'] + "'" + "," + \
              "test_jg=" + "'" + message['test_jg'] + "'" + "," + \
              "test_zj=" + "'" + message['test_zj'] + "'" + "," + \
              "report_starttime=" + "'" + message['report_starttime'] + "'" + "," + \
              "report_endtime=" + "'" + message['report_endtime'] + "'" + "," + \
              "other=" + "'" + message['other'] + "'" + \
              "where pro_index=" + "'" + message['pro_index'] + "'"
        print(sql)
        c.execute(sql)
        conn.commit()
        conn.close()

        sql = "select * from report"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        return jsonify(rows)


@app.route('/report_search', methods=['GET', 'POST'])
def report_search():
    if request.method == 'POST':
        message = request.get_json()
        if message['query'] == '':
            sql = "select * from report"
        else:
            sql = "select * from report where report_name = " + "'" + message['query'] + "'"
        rows = query_db(sql)
        rows = [dict(row) for row in rows]
        # print(jsonify(rows))
        # print(rows)
        return jsonify(rows)














@app.route('/')
def index():
    if 'user' in session:
        return render_template('hello.html', name=session['user'])

    else:
        return redirect(url_for('login'))

@app.route('/login', methods=['POST', 'GET'])
def login():
    if request.method == 'POST':
        name = request.form['user']
        passwd = request.form['passwd']
        cursor = g.db.execute('select * from users where name=? and password=?', [name, passwd])
        if cursor.fetchone() is not None:
            session['user'] = name
            flash('Login successfully!')
            return redirect(url_for('index'))
        else:
            flash('No such user!', 'error')
            return redirect(url_for('login'))
    else:
        return render_template('login.html')

@app.route('/logout')
def logout():
    session.pop('user', None)
    return redirect(url_for('login'), 302)

if __name__ == '__main__':
    app.run()